﻿using System.Collections;

using System.Collections.Generic;

using UnityEngine;

using System.Data;

using System.IO;

using Excel;
using OfficeOpenXml;

public class DoExcel
{
    #region 读
    /// <summary>
    /// 读表
    /// </summary>
    /// <param name="path"></param>
    /// <param name="versions"></param>
    /// <returns></returns>
    public static DataSet ReadExcel(string path, EVersions versions)
    {
        FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);
        IExcelDataReader excelReader = null;
        switch (versions)
        {
            case EVersions.A1997To2003:
                //1997-2003xls
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                break;
            case EVersions.A2007://xlsx
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                break;
            default:
                break;
        }
        DataSet result = excelReader.AsDataSet();

        excelReader.Close();

        return result;

    }
    /// <summary>
    /// 获取全部表名称
    /// </summary>
    /// <param name="path"></param>
    /// <param name="versions"></param>
    /// <returns></returns>
    public static List<DataTable> ReadTablesData(string path, EVersions versions)
    {
        DataSet resultds = ReadExcel(path, versions);
        List<DataTable> vs = new List<DataTable>();
        for (int i = 0; i < resultds.Tables.Count; i++)
        {
            vs.Add(resultds.Tables[i]);
        }
        return vs;
    }

    public static Dictionary<string, ExcelData> GetExcelData(string fileName, EVersions versions)
    {
        Dictionary<string, ExcelData> DataInfo = new Dictionary<string, ExcelData>();
        List<DataTable> dataTables = ReadTablesData(fileName, versions);
        List<List<string>> tempExcel = new List<List<string>>();
        for (int k = 0; k < dataTables.Count; k++)
        {
            DataTable tabName = dataTables[k];
            int sumColumn = tabName.Columns.Count;
            int sumRow = tabName.Rows.Count;
            for (int i = 0; i < sumRow; i++)
            {
                if (!string.IsNullOrEmpty(tabName.Rows[i][0].ToString()))
                {
                    List<string> rowContentent = new List<string>();
                    for (int j = 0; j < sumColumn; j++)
                    {
                        rowContentent.Add(tabName.Rows[i][j].ToString());
                    }
                    tempExcel.Add(rowContentent);
                }
            }
            ExcelData excelData = new ExcelData();
            excelData.ExcelContent = tempExcel;
            DataInfo.Add(tabName.TableName, excelData);
        }
        return DataInfo;
    }
    #endregion
    #region 写
    public static void WriteExcel(string _filePath, Dictionary<string, ExcelData> data)
    {
        if (File.Exists(_filePath))
        {
            string ext = Path.GetExtension(_filePath);
            string fileNmae = Path.GetFileName(_filePath);
            fileNmae = fileNmae + "(ZAZ)";
            _filePath = fileNmae + ext;
        }
        FileInfo _excelName = new FileInfo(_filePath);
        if (_excelName.Exists)
        {
            //删除旧文件，并创建一个新的 excel 文件。
            _excelName.Delete();
            _excelName = new FileInfo(_filePath);
        }

        //通过ExcelPackage打开文件
        using (ExcelPackage package = new ExcelPackage(_excelName))
        {
            foreach (var item in data)
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(item.Key);
                for (int i = 0; i < item.Value.ExcelContent.Count; i++)
                {
                    for (int J = 0; J < item.Value.ExcelContent[i].Count; J++)
                    {
                        worksheet.Cells[i + 1, J + 1].Value = item.Value.ExcelContent[i][J];
                    }

                    worksheet.Cells[1, 2].Value = "姓名";
                    worksheet.Cells[1, 3].Value = "性别";
                }
                //保存excel
                package.Save();
            }
        }
        #endregion
    }
}
#region 注释功能
///// <summary>
///// 获取全部表名称
///// </summary>
///// <param name="path"></param>
///// <param name="versions"></param>
///// <returns></returns>
//public static List<string> ReadTablesName(string path, EVersions versions)
//{
//    DataSet resultds = ReadExcel(path, versions);
//    List<string> vs = new List<string>();
//    for (int i = 0; i < resultds.Tables.Count; i++)
//    {
//        vs.Add(resultds.Tables[i].TableName);
//    }
//    return vs;
//}

///// <summary>
///// 读表
///// </summary>
///// <param name="path"></param>
///// <param name="versions"></param>
///// <param name="tabName"></param>
///// <param name="column"></param>
///// <param name="row"></param>
///// <returns></returns>
//public static List<DepenceTableData> ReadTab(string path, EVersions versions, DataTable tabName, int column, int row = -1)
//{
//    List<DepenceTableData> _data = new List<DepenceTableData>();

//    int sumColumn = tabName.Columns.Count;

//    int sumRow = tabName.Rows.Count;

//    if (row == -1)
//    {
//        for (int i = 1; i < sumRow; i++)
//        {
//            if (column >= sumRow - 1)
//            {
//                continue;
//            }
//            DepenceTableData temp_data;
//            temp_data.Content = tabName.Rows[i][column].ToString();
//            _data.Add(temp_data);
//        }
//    }
//    else
//    {
//        if (column >= sumRow - 1)
//        {

//        }
//        else
//        {
//            DepenceTableData temp_data;
//            temp_data.Content = tabName.Rows[row][column].ToString();
//            _data.Add(temp_data);
//        }

//    }
//    return _data;
//}
//public static string ReadAllTab(string path, EVersions versions, DataTable tabName)
//{
//    int sumColumn = tabName.Columns.Count;
//    string str = "";
//    int sumRow = tabName.Rows.Count;
//    for (int i = 1; i < sumRow; i++)
//    {
//        for (int j = 0; j < sumColumn; j++)
//        {

//            if (j >= sumColumn - 1)
//            {
//                str += "   " + tabName.Rows[i][j].ToString() + "\r\n";
//            }
//            else
//            {
//                str += "   " + tabName.Rows[i][j].ToString();
//            }

//        }
//    }
//    return str;
//}
#endregion